#include "StdAfx.h"
#include <string>
#include <Util/StringUtil.h>
#include <interface/data_op/table_wrapper.h>
#include "mail_op.h"

namespace aot{ namespace data{

mail_op_impl::mail_op_impl(void)
{
}

mail_op_impl::~mail_op_impl(void)
{
}

bool mail_op_impl::insert(const imail_info* p, int* new_id)
{
    if (!g_db_user) return false;

    std::string str_mail_title = p->mail_title->c_str();
    std::string str_mail_from = p->mail_from->c_str();
    std::string str_mail_to = p->mail_to->c_str();
    std::string str_recv_datetime = p->recv_datetime->c_str();
    std::string str_body = p->body->c_str();

    remove_sin_quot(str_mail_title);
    remove_sin_quot(str_mail_from);
    remove_sin_quot(str_mail_to);
    remove_sin_quot(str_recv_datetime);
    remove_sin_quot(str_body);

    std::string sql_fmt = 
     "insert into mails \
     (id,\
     is_read,\
     mail_title,\
     mail_from, \
     mail_to,\
     recv_datetime,\
     body) \
     values\
     (%d, %d, '%s', '%s', '%s', '%s', '%s' );";

    std::string sql_exe;
    int t_new_id = get_table_next_id("mails");

    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
     t_new_id,
     p->is_read,
     str_mail_title.c_str(),
     str_mail_from.c_str(),
     str_mail_to.c_str(),
     str_recv_datetime.c_str(),
     str_body.c_str()
     );

    *new_id = t_new_id;
    return g_db_user->exec(sql_exe.c_str());
}

bool mail_op_impl::del(int id)
{
    std::string sql_fmt = "delete from mails where id=%d";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), id);
    return g_db_user->exec(sql_exe.c_str());
}

bool mail_op_impl::update(const imail_info* p)
{
    if (!g_db_user) return false;

    std::string str_mail_title = p->mail_title->c_str();
    std::string str_mail_from = p->mail_from->c_str();
    std::string str_mail_to = p->mail_to->c_str();
    std::string str_recv_datetime = p->recv_datetime->c_str();
    std::string str_body = p->body->c_str();

    remove_sin_quot(str_mail_title);
    remove_sin_quot(str_mail_from);
    remove_sin_quot(str_mail_to);
    remove_sin_quot(str_recv_datetime);
    remove_sin_quot(str_body);

    std::string sql_fmt = 
     "update mails set \
     is_read=%d,\
     mail_title='%s',\
     mail_from='%s', \
     mail_to='%s',\
     recv_datetime='%s',\
     body='%s' \
     where id=%d;";

    std::string sql_exe;
    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
     p->is_read,
     str_mail_title.c_str(),
     str_mail_from.c_str(),
     str_mail_to.c_str(),
     str_recv_datetime.c_str(),
     str_body.c_str(),
     p->id
     );

    return g_db_user->exec(sql_exe.c_str());
}

bool
mail_op_impl::read(int id, isqlite3_table** out)
{
    std::string sql_fmt = " select \
                         id,\
                         mail_title,\
                         mail_from,\
                         mail_to,\
                         recv_datetime,\
                         body, \
                         is_read \
                         from mails \
                         where id=%d;";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), id);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
mail_op_impl::read(int id, imail_info* out)
{
 aot::data::table_helper t_table;
 bool ret =false;
 if (this->read(id, t_table))
 {
     if (t_table.get_rows_count() > 0)
     {
         t_table.set_row_index(0);

         out->id = t_table.get_field_value_int("id");
         out->is_read = t_table.get_field_value_int("is_read");

         out->mail_title->assign(t_table.get_field_value_str("mail_title").c_str());
         out->mail_from->assign(t_table.get_field_value_str("mail_from").c_str());
         out->mail_to->assign(t_table.get_field_value_str("mail_to").c_str());
         out->recv_datetime->assign(t_table.get_field_value_str("recv_datetime").c_str());
         out->body->assign(t_table.get_field_value_str("body").c_str());

         ret = true;
     }
 }
 return ret;
}

bool
mail_op_impl::read(int id, imail_info** out)
{
 aot::data::imail_info* obj = new aot::data::mail_info_impl();
 *out = obj;
 return this->read(id, obj);
}

bool
mail_op_impl::get_mails_by_email(const char* mail, isqlite3_table** out)
{
 std::string sql_fmt = "select \
                         id,\
                         mail_title,\
                         mail_from,\
                         mail_to,\
                         recv_datetime,\
                         body, \
                         is_read \
                         from mails \
                         where mail_to='%s'";
 std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail);
 return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
mail_op_impl::get_mails_by_email_and_read_flag(const char* mail, int read_flag, isqlite3_table** out)
{
 std::string sql_fmt = "select \
                        id,\
                        mail_title,\
                        mail_from,\
                        mail_to,\
                        recv_datetime,\
                        body, \
                        is_read \
                        from mails \
                        where mail_to='%s' and is_read=%d";
 std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail, read_flag);
 return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
mail_op_impl::get_mails_by_sender(const char* sender_email, isqlite3_table** out)
{
 std::string sql_fmt = "select \
                        id,\
                        mail_title,\
                        mail_from,\
                        mail_to,\
                        recv_datetime,\
                        body, \
                        is_read \
                        from mails \
                        where mail_from='%s';";
 std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), sender_email);
 return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

int
mail_op_impl::get_count_by_email(const char* mail)
{
    int total;
    std::string sql_fmt = "select count(*) as t from mails where mail_to='%s';";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail);
    total = g_db_user->exec_scalar(sql_exe.c_str());
    return total;
}

bool
mail_op_impl::get_page_mails_by_email(const char* mail, int count_per_page, int page_index, isqlite3_table** out)
{
    int total = get_count_by_email(mail);
    int limit;
    int offset;
    this->cal_page_info(total, count_per_page, page_index, limit, offset);

    std::string sql_fmt = "select \
                       id,\
                       mail_title,\
                       mail_from,\
                       mail_to,\
                       recv_datetime,\
                       body, \
                       is_read \
                       from mails \
                       where mail_to='%s' order by id limit %d offset %d;";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail, limit, offset);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

int
mail_op_impl::get_count_by_keyword(const char* mail, const char* keyword)
{
    int total;
    std::string sql_fmt = "select count(*) as t from mails where mail_to='%s' and mail_title like '%s';";

    std::string str_keyword = keyword;
    remove_sin_quot(str_keyword);
    str_keyword = "%" + str_keyword + "%";

    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail, str_keyword.c_str());
    total = g_db_user->exec_scalar(sql_exe.c_str());
    return total;
}

bool
mail_op_impl::get_page_mails_by_keyword(const char* mail, const char* keyword, int count_per_page, int page_index, isqlite3_table** out)
{
    int total = get_count_by_keyword(mail, keyword);
    int limit;
    int offset;
    this->cal_page_info(total, count_per_page, page_index, limit, offset);

    std::string sql_fmt = "select \
                        id,\
                        mail_title,\
                        mail_from,\
                        mail_to,\
                        recv_datetime,\
                        body, \
                        is_read \
                        from mails \
                        where mail_to='%s' and mail_title like '%s' and order by id limit %d offset %d;";
    std::string str_keyword = keyword;
    remove_sin_quot(str_keyword);
    str_keyword = "%" + str_keyword + "%";

    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail, str_keyword.c_str(), limit, offset);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}


bool
mail_op_impl::add_account(const imail_account_info* p)
{
    if (!g_db_user) return false;

    std::string str_email = p->email->c_str();
    std::string str_mail_server = p->mail_server->c_str();
    std::string str_smtp = p->smtp->c_str();
    std::string str_mail_account = p->mail_account->c_str();
    std::string str_mail_pwd = p->mail_pwd->c_str();
    std::string str_create_datetime = p->create_datetime->c_str();

    remove_sin_quot(str_email);
    remove_sin_quot(str_mail_server);
    remove_sin_quot(str_smtp);
    remove_sin_quot(str_mail_account);
    remove_sin_quot(str_mail_pwd);
    remove_sin_quot(str_create_datetime);

    std::string sql_fmt = 
     "insert into email_account \
     (active,\
     mail_type,\
     mail_server,\
     smtp, \
     mail_account,\
     mail_pwd,\
     create_datetime,\
     email) \
     values\
     (%d, %d, '%s', '%s', '%s', '%s', '%s', '%s');";

    std::string sql_exe;

    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
     p->active,
     p->mail_type,
     str_mail_server.c_str(),
     str_smtp.c_str(),
     str_mail_account.c_str(),
     str_mail_pwd.c_str(),
     str_create_datetime.c_str(),
     str_email.c_str()
     );

    return g_db_user->exec(sql_exe.c_str());

}

bool
mail_op_impl::del_account(const char* mail)
{
    std::string sql_fmt = "delete from email_account where email='%s';";
    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail);
    return g_db_user->exec(sql_exe.c_str());
}

bool
mail_op_impl::update_account(const imail_account_info* p)
{
    if (!g_db_user) return false;

    std::string str_email = p->email->c_str();
    std::string str_mail_server = p->mail_server->c_str();
    std::string str_smtp = p->smtp->c_str();
    std::string str_mail_account = p->mail_account->c_str();
    std::string str_mail_pwd = p->mail_pwd->c_str();
    std::string str_create_datetime = p->create_datetime->c_str();

    remove_sin_quot(str_email);
    remove_sin_quot(str_mail_server);
    remove_sin_quot(str_smtp);
    remove_sin_quot(str_mail_account);
    remove_sin_quot(str_mail_pwd);
    remove_sin_quot(str_create_datetime);

    std::string sql_fmt = 
     "update email_account set \
     active=%d,\
     mail_type=%d,\
     mail_server='%s',\
     smtp='%s', \
     mail_account='%s',\
     mail_pwd='%s',\
     create_datetime='%s' where \
     email='%s';";

    std::string sql_exe;
    sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(),
     p->active,
     p->mail_type,
     str_mail_server.c_str(),
     str_smtp.c_str(),
     str_mail_account.c_str(),
     str_mail_pwd.c_str(),
     str_create_datetime.c_str(),
     str_email.c_str()
     );

    return g_db_user->exec(sql_exe.c_str());
}

bool
mail_op_impl::read_all_account( isqlite3_table** out)
{
    std::string sql_fmt = " select \
                         mail_server,\
                         smtp,\
                         mail_account,\
                         mail_pwd,\
                         create_datetime,\
                         active, \
                         mail_type, \
                         email \
                         from email_account;";

    return g_db_user->exec_and_get_record(sql_fmt.c_str(), out);
}

bool
mail_op_impl::read_account(const char* mail, isqlite3_table** out)
{
    std::string sql_fmt = "select \
                         mail_server,\
                         smtp,\
                         mail_account,\
                         mail_pwd,\
                         create_datetime,\
                         active, \
                         mail_type, \
                         email \
                         from email_account where email='%s';";

    std::string sql_exe = aot::tt::utility::StrUtil::Printf(sql_fmt.c_str(), mail);
    return g_db_user->exec_and_get_record(sql_exe.c_str(), out);
}

bool
mail_op_impl::read_account(const char* mail, imail_account_info* out)
{
    aot::data::table_helper t_table;
    bool ret =false;
    if ( this->read_account(mail, t_table) )
    {
     if ( t_table.get_rows_count() > 0 )
     {
         t_table.set_row_index(0);

         out->active = t_table.get_field_value_int("active");
         out->mail_type = t_table.get_field_value_int("mail_type");

         out->mail_server->assign(t_table.get_field_value_str("mail_server").c_str());
         out->smtp->assign(t_table.get_field_value_str("smtp").c_str());
         out->mail_account->assign(t_table.get_field_value_str("mail_account").c_str());
         out->mail_pwd->assign(t_table.get_field_value_str("mail_pwd").c_str());
         out->create_datetime->assign(t_table.get_field_value_str("create_datetime").c_str());
         out->email->assign(t_table.get_field_value_str("email").c_str());
         ret = true;
     }
    }
    return ret;
}

bool
mail_op_impl::read_account(const char* mail, imail_account_info** out)
{
    aot::data::imail_account_info* obj = new aot::data::mail_account_info_impl;
    *out = obj;
    return this->read_account(mail, obj);
}


}} /* end namespace aot/data*/
